# LuckPay Web Manage System
#
# Copyright (c) 2016 Lucky Byte, Inc.
#
express = require 'express'
pgsql   = require 'pg'
uuid    = require 'uuid'
router = express.Router()
module.exports = router

router.all '/', (req, res, next) ->
    where_clause = """(
        serial::text like $1 or uuid like $1 or notes like $1 or
        trinfo::json#>>'{"code"}' like $1 or
        trinfo::json#>>'{"name"}' like $1 or
        i_pkt::json#>>'{"2"}' like $1 or
        i_pkt::json#>>'{"3"}' like $1 or
        i_pkt::json#>>'{"4"}' like $1 or
        i_pkt::json#>>'{"11"}' like $1 or
        i_pkt::json#>>'{"41"}' like $1 or
        i_pkt::json#>>'{"42"}' like $1 or
        o_pkt::json#>>'{"37"}' like $1 or
        i_pkt::json#>>'{"merId"}' like $1 or
        i_pkt::json#>>'{"termId"}' like $1 or
        i_pkt::json#>>'{"accNo"}' like $1 or
        i_pkt::json#>>'{"orderId"}' like $1 or
        o_pkt::json#>>'{"queryId"}' like $1
    )"""
    params = []
    params.push '%' + res.locals.keyword + '%'
    index = 2

    # 通过渠道过滤
    chnl = req.body.chnl or req.query.chnl or ''
    if chnl.length > 0
        where_clause += " and chnl = $#{index}"
        index += 1
        params.push chnl
        res.locals.chnl = chnl

    today = moment().format('YYYY-MM-DD')

    # 如果没有提供日期，则仅查询当日
    start_date = req.body.start_date or req.query.start_date or today
    end_date = req.body.end_date or req.query.end_date or today

    # 如果查询起始日期和结束日期都是当前的话，则查询当前流水表，否则查询历史流水表
    table_name = 'pay_trlist_hist'
    if start_date is today and end_date is today
        table_name = 'pay_trlist'

    where_clause += " and sys_time::date >= '#{start_date}'"
    res.locals.start_date = start_date
    where_clause += " and sys_time::date <= '#{end_date}'"
    res.locals.end_date = end_date

    # 通过交易控制代码过滤
    trcode = req.body.trcode or req.query.trcode or ''
    if trcode.length > 0
        where_clause += """ and trinfo::json#>>'{"code"}' = '#{trcode}'"""
        res.locals.trcode = trcode

    # 通过平台收发标志过滤
    incoming = req.body.incoming or req.query.incoming or ''
    if incoming.length > 0
        where_clause += " and incoming = $#{index}"
        index += 1
        params.push incoming is 'true'
        res.locals.incoming = incoming is 'true'

    # 通过是否清算标志过滤
    settl = req.body.settl or req.query.settl or ''
    if settl.length > 0
        where_clause += """ and trinfo::json#>>'{"settl"}' = $#{index}"""
        index += 1
        params.push settl is 'true'
        res.locals.settl = settl is 'true'

    # 通过成功或失败状态过滤
    succed = req.body.succed or req.query.succed or ''
    if succed.length > 0
        if succed is 'true'
            where_clause += """ and status = 0 and
            case
            when chnl in (1,4) then
                ( o_pkt is not null and
                json_extract_path_text(o_pkt, '39') = '00')
            when chnl = 3 then
                ( n_pkt is not null and
                json_extract_path_text(n_pkt, 'respCode') = '00')
            end
            """
        else
            where_clause += """ and (status <> 0 or
            case
            when chnl in (1,4) then
                ( o_pkt is null or
                json_extract_path_text(o_pkt, '39') <> '00')
            when chnl = 3 then
                ( n_pkt is null or
                json_extract_path_text(n_pkt, 'respCode') <> '00')
            end)
            """
        res.locals.succed = succed

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 查询记录总数
    await client.query \
        "select count(*) as count from #{table_name} where #{ where_clause }",
        params, defer err, result
    if err then done(client); return next(err)
    res.locals.total = result.rows[0].count

    # 查询当前页面中显示的记录
    params.push res.locals.offset
    params.push res.locals.page_rows
    offset_index = params.length - 1
    limit_index = params.length
    await client.query \
        "select *,
            case
            when chnl in (1,4) then
                array[
                    json_extract_path_text(i_pkt, '42'),
                    json_extract_path_text(i_pkt, '41'),
                    json_extract_path_text(i_pkt, '4'),
                    json_extract_path_text(i_pkt, '11'),
                    json_extract_path_text(o_pkt, '15')
                ]
            when chnl = 3 then
                array[
                    json_extract_path_text(i_pkt, 'merId'),
                    json_extract_path_text(i_pkt, 'termId'),
                    json_extract_path_text(i_pkt, 'txnAmt'),
                    json_extract_path_text(n_pkt, 'traceNo'),
                    case when n_pkt is not null then
                        json_extract_path_text(n_pkt, 'settleDate')
                    else
                        json_extract_path_text(i_pkt, 'settleDate')
                    end
                ]
            end as trdata
         from #{table_name} where #{ where_clause }
            order by serial desc
            offset $#{offset_index}::int limit $#{limit_index}::int",
        params, defer err, result
    if err then done(client); return next(err)
    done()

    # 省略账号的中间部分
    fmt_pan = (pri_acct) ->
        if not pri_acct or pri_acct.length < 9
            return pri_acct
        len = pri_acct.length
        pri_acct = pri_acct.substring(0, 4) + ' .. ' +
            pri_acct.substring(len - 4, len)

    # 获取主账号
    for record in result.rows
        switch record.chnl
            when 1, 4
                if record.i_pkt['2']
                    record.pri_acct = fmt_pan(record.i_pkt['2'])
                    break
                if record.o_pkt and record.o_pkt['2']
                    record.pri_acct = fmt_pan(record.o_pkt['2'])
                    break;
                if record.i_pkt['36']
                    record.pri_acct = record.i_pkt['36'].substring(2, 10) + ' ...'
                    break
                if record.i_pkt['35']
                    record.pri_acct = record.i_pkt['35'].substring(0, 8) + ' ...'
                    break
            when 3
                if record.i_pkt.accNo
                    record.pri_acct = fmt_pan(record.i_pkt.accNo)

    res.render 'trlist/index', records: result.rows
